package com.ht.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ht.bean.Pager;
import com.ht.bean.Pager4EasyUI;
import com.ht.bean.Product;

public class ProductDAOImpl extends BaseDAO implements ProductDAO {

	@Override
	public Product add(Product product) {
		Connection conn = getConn();
		try {
			PreparedStatement ps = conn.prepareStatement("insert into product(name,price)values(?,?)");
			ps.setString(1, product.getName());
			ps.setDouble(2, product.getPrice());
			ps.execute();
			ps.close();
			conn.close();
			return product;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Product query(String id) {
		return null;
	}

	@Override
	public List<Product> queryAll() {
		Connection conn = getConn();
		List<Product> products = new ArrayList<>();
		try {
			PreparedStatement ps = conn.prepareStatement("select * from product");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Product product = new Product();
				product.setId(rs.getString("id"));
				product.setName(rs.getString("name"));
				product.setPrice(rs.getDouble("price"));
				products.add(product);
			}
			rs.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return products;
	}

	@Override
	public void remove(String id) {
		Connection conn = getConn();
		try {
			PreparedStatement ps = conn.prepareStatement("delete from product where id = ?");
			ps.setString(1, id);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	@Override
	public Product update(Product product) {
		Connection conn = getConn();
		try {
			PreparedStatement ps = conn.prepareStatement("update product set name = ?, price = ? where id = ?");
			ps.setString(1, product.getName());
			ps.setDouble(2, product.getPrice());
			ps.setString(3, product.getId());
			ps.execute();
			ps.close();
			conn.close();
			return product;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Pager<Product> queryByPager(int pageNo, int pageSize) {
		Pager<Product> pager = new Pager<Product>(pageNo, pageSize);
		Connection conn = getConn();
		try {
			PreparedStatement ps = conn.prepareStatement("select * from product limit ?, ?");
			ps.setInt(1, pager.getBeginIndex());
			ps.setInt(2, pager.getPageSize());
			ResultSet rs = ps.executeQuery();
			List<Product> products = new ArrayList<>();
			while (rs.next()) {
				Product product = new Product();
				product.setId(rs.getString("id"));
				product.setName(rs.getString("name"));
				product.setPrice(rs.getDouble("price"));
				products.add(product);
			}
			pager.setResult(products);
			pager.setTotal(count());
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pager;
	}

	@Override
	public int count() {
		Connection conn = getConn();
		int count = 0;
		try {
			PreparedStatement ps = conn.prepareStatement("select count(1) from product");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public Pager4EasyUI<Product> paging(int pageNo, int pageSize) {
		Connection conn = getConn();
		Pager4EasyUI<Product> pager = new Pager4EasyUI<Product>();
		pager.setPageNo(pageNo);
		pager.setPageSize(pageSize);
		try {
			PreparedStatement ps = conn.prepareStatement("select * from product limit ?, ?");
			ps.setInt(1, pager.getBeginIndex());
			ps.setInt(2, pager.getPageSize());
			ResultSet rs = ps.executeQuery();
			List<Product> products = new ArrayList<>();
			while (rs.next()) {
				Product product = new Product();
				product.setId(rs.getString("id"));
				product.setName(rs.getString("name"));
				product.setPrice(rs.getDouble("price"));
				products.add(product);
			}
			pager.setRows(products);
			pager.setTotal(count());
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pager;
	}

}
